using System;
using System.Collections.Generic;
using System.Text;
using QuanLyKhachSan.DTO;
using System.Data;

namespace QuanLyKhachSan
{
    public class LoaiKhachHangDAO
    {
        
        // Them Loai Khach Hang
        public static bool Insert(LoaiKhachHangDTO  loaiKHdto)
        {
            string sql = "INSERT INTO LoaiKhachHang  VALUES (N'" + loaiKHdto.TenLoai + "'," + loaiKHdto.HeSoNhan  + ",0)";
            DataAccessHelper s = new SqlDataAccessHelper();
            int kq = s.ExecuteNonQuery1(sql);
            if (kq < 0)
                return false;
            return true;
        }

        
        // Xoa Loai Khach Hang
        public static bool Delete(int maLoai)
        {
            string sqlSelect = "Select maLoaiKH from KhachHang where maLoaiKH=" + maLoai;
            DataAccessHelper s = new SqlDataAccessHelper();
            DataTable dt = s.ExecuteQuery(sqlSelect);

            string sql = "UPDATE LoaiKhachHang SET danhDau=1 WHERE maLoaiKH=" + maLoai ;
            if (dt.Rows.Count <= 0)           // Khong co khoa ngoai tham chieu toi maLoai thi xoa di,nguoc lai danh dau =1
            {
                sql = "DELETE from LoaiKhachHang WHERE maLoaiKH=" + maLoai;
            }

            int kq = s.ExecuteNonQuery1(sql);
            if (kq < 0)
                return false;
            return true;
        }

        // Cap nhat thong tin Loai Khach Hang
        public static bool Update(LoaiKhachHangDTO loaiKHdto)
        {
            string sql = "UPDATE LoaiKhachHang SET tenLoai=N'" + loaiKHdto.TenLoai + "',heSoNhan=" + loaiKHdto.HeSoNhan + " WHERE maLoaiKH=" + loaiKHdto.MaLoaiKH ;
            DataAccessHelper s = new SqlDataAccessHelper();

            int kq = s.ExecuteNonQuery1(sql);
            if (kq < 0)
                return false;
            return true;
        }
        
        // Lay danh sach loai khach hang
        public static DataTable LayDSLoaiKH()
        {
            DataTable dt=new DataTable();
            string sql = "Select MaLoaiKhachHang=maLoaiKH,TenLoaiKhachHang=tenLoai,HeSoNhan=heSoNhan from LoaiKhachHang where danhDau=0";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            return dt;
        }
        // Lay danh sach loai khach hang theo ten loai khach hang
        public static DataTable LayDSLoaiKHTheoTen(string tenLoai)
        {
            DataTable dt = new DataTable();
            string sql = "Select MaLoaiKhachHang=maLoaiKH,TenLoaiKhachHang=tenLoai,HeSoNhan=heSoNhan from LoaiKhachHang where tenLoai =N'" + tenLoai + "' and danhDau=0";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            return dt;
        }
        public static LoaiKhachHangDTO LayLoaiKhachHangTheoMaLoai(int maLoaiKH)
        {
            DataTable dt = new DataTable();
            string sql = "Select * from LoaiKhachHang where maLoaiKH="+ maLoaiKH +" and danhDau=0";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            DataRow dr = dt.Rows[0];
            LoaiKhachHangDTO loaiKhachHangdto = new LoaiKhachHangDTO();
            loaiKhachHangdto.MaLoaiKH = int.Parse(dr["maLoaiKH"].ToString());
            loaiKhachHangdto.TenLoai = dr["tenLoai"].ToString();
            return loaiKhachHangdto ;
        }
        public static LoaiKhachHangDTO LayLoaiKhachHangTheoTenLoaiKH(string tenLoaiKH)
        {
            DataTable dt = new DataTable();
            string sql = "Select * from LoaiKhachHang where tenLoai=N'" + tenLoaiKH  + "' and danhDau=0";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            DataRow dr = dt.Rows[0];
            LoaiKhachHangDTO loaiKhachHangdto = new LoaiKhachHangDTO();
            loaiKhachHangdto.MaLoaiKH = int.Parse(dr["maLoaiKH"].ToString());
            loaiKhachHangdto.TenLoai = dr["tenLoai"].ToString();
            return loaiKhachHangdto;
        }
        public static DataTable LayHeSoTheoTenLoaiKH(string tenLoaiKH)
        {
            DataTable dt = new DataTable();
            string sql = "Select heSoNhan from LoaiKhachHang where tenLoai=N'" + tenLoaiKH + "' and danhDau=0";
            DataAccessHelper con = new SqlDataAccessHelper();
            dt = con.ExecuteQuery(sql);
            return dt;
        }
    }
} 
